﻿USE [BizTalkDTADb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Stéphane Aubonnet
-- Create date: 2014-10-01
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Onatica_GetServiceFlows]
	@ServiceInstanceId uniqueidentifier = NULL,
	@dayPast int = 1,
	@startDate DateTime = NULL,
	@endDate DateTime = NULL,
	@includeArtefacts bit = 0,
	@includeLinkInstances bit = 0
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @RootServiceInstanceId AS uniqueidentifier;

	DECLARE @Links TABLE (
	[FowTemplate] [nvarchar](256) NOT NULL,
	[FromEventId] [bigint] NOT NULL,
	[FromMessageInstanceId] [uniqueidentifier] NOT NULL,
	[FromServiceInstanceId] [uniqueidentifier] NOT NULL,
	[FromStatus] [int] NOT NULL,
	[FromComponentId] [int] NOT NULL,
	[FromComponentName] [nvarchar](256) NOT NULL,
	[FromServiceName] [nvarchar](256) NOT NULL,
	[FromServiceType] [nvarchar](256) NOT NULL,
	[FromActivityId] [uniqueidentifier] NULL,
	[FromTimestamp] [datetime] NOT NULL,
	[FromIs2Way] [int] NOT NULL,
	[ToEventId] [bigint] NOT NULL,
	[ToMessageInstanceId] [uniqueidentifier] NOT NULL,
	[ToServiceInstanceId] [uniqueidentifier] NOT NULL,
	[ToStatus] [int] NOT NULL,
	[ToComponentId] [int] NOT NULL,
	[ToComponentName] [nvarchar](256) NOT NULL,
	[ToServiceName] [nvarchar](256) NOT NULL,
	[ToServiceType] [nvarchar](256) NOT NULL,
	[ToActivityId] [uniqueidentifier] NULL,
	[ToTimestamp] [datetime] NOT NULL,
	[MessageServiceInstance] [uniqueidentifier] NULL,
	[FlowId] [uniqueidentifier] NOT NULL,
	[Generation] [int] NOT NULL
	)

	IF @ServiceInstanceId IS NOT NULL
		BEGIN
			SET @RootServiceInstanceId = dbo.Onatica_GetRootServiceInstanceId (@ServiceInstanceId);
			--SET @RootServiceInstanceId = @ServiceInstanceId;
		END
	ELSE
		BEGIN
			IF @endDate IS NULL  
				BEGIN 
					SET @endDate = GETUTCDATE();
				END;

			IF @startDate IS NULL  
				BEGIN 
					SET @startDate = DATEADD (DAY,- @dayPast, @endDate);
				END;
		END;

	WITH cteLinkedEvents AS
	(
		SELECT
			 mioe.nEventId
			,mioe.nPortId
			,mioe.uidServiceInstanceId
			,mioe.uidActivityId
			,mioe.uidMessageInstanceId
			,mioe.nStatus
			,mioe.dtTimestamp
			,srvI.uidServiceId
			,srvI.ErrorInfo
			,port.strPortName
			,srv.strServiceType
			,srv.strServiceName
		From
			dta_MessageInOutEvents mioe WITH (NOLOCK)
			INNER JOIN dta_ServiceInstances srvI WITH (NOLOCK)
				ON mioe.uidServiceInstanceId = srvI.uidServiceInstanceId
				AND mioe.uidActivityId = srvI.uidActivityId
			INNER JOIN dta_PortName port WITH (NOLOCK)
				ON mioe.nPortId = port.nPortId
			INNER JOIN dta_Services srv WITH (NOLOCK)
				ON srvI.uidServiceId = srv.uidServiceId
	)
	,cteLinks AS
	(
	Select 
		FromEvt.strPortName FowTemplate,
		FromEvt.nEventId FromEventId,
		FromEvt.uidMessageInstanceId FromMessageInstanceId,
		FromEvt.uidServiceInstanceId FromServiceInstanceId,
		FromEvt.nStatus FromStatus,
		FromEvt.strPortName FromComponentName,
		FromEvt.strServiceName FromServiceName,
		FromEvt.strServiceType FromServiceType,
		FromEvt.uidActivityId FromActivityId,
		FromEvt.dtTimestamp FromTimestamp,
		0 FromIs2Way,
		ToEvt.nEventId ToEventId,
		ToEvt.uidMessageInstanceId ToMessageInstanceId,
		ToEvt.uidServiceInstanceId ToServiceInstanceId,
		ToEvt.nStatus ToStatus,
		CASE 
			WHEN ToEvt.strServiceType = 'Orchestration' THEN ToEvt.strServiceName 
			ELSE ToEvt.strPortName
		END  ToComponentName,
		ToEvt.strServiceName ToServiceName,
		ToEvt.strServiceType ToServiceType,
		ToEvt.uidActivityId ToActivityId,
		ToEvt.dtTimestamp ToTimestamp,
		ToEvt.uidServiceInstanceId MessageServiceInstance,
		NEWID() FlowId,
		0 Generation
	From 
			cteLinkedEvents FromEvt
		INNER JOIN cteLinkedEvents ToEvt
			ON FromEvt.uidMessageInstanceId = ToEvt.uidMessageInstanceId
			AND FromEvt.uidServiceInstanceId <> ToEvt.uidServiceInstanceId
		INNER JOIN BizTalkMgmtDb.dbo.bts_receiveport ReceivePort
			ON ReceivePort.nvcName = FromEvt.strPortName
	where 
			(FromEvt.strServiceType = 'Pipeline'
			AND FromEvt.nStatus = 1 AND ToEvt.nStatus = 0
			AND FromEvt.dtTimestamp between @startDate AND @endDate
			AND @ServiceInstanceId IS NULL) 
		OR
			FromEvt.uidServiceInstanceId = @RootServiceInstanceId
		--For test
		--AND FromEvt.strPortName = 'MDM.Core.Propagation.GenericAsync'
		--AND FromEvt.uidServiceInstanceId = 'f66f8c91-d8ee-4b1f-a276-4f53605e64b3'
		 
UNION ALL
	Select
		FromEvt.FowTemplate FowTemplate,
		FromEvt.ToEventId FromEventId,
		FromEvt.ToMessageInstanceId FromMessageInstanceId,
		FromEvt.ToServiceInstanceId FromServiceInstanceId,
		FromEvt.ToStatus FromStatus,
		FromEvt.ToComponentName FromComponentName,
		FromEvt.ToServiceName FromServiceName,
		FromEvt.ToServiceType FromServiceType,
		FromEvt.ToActivityId FromActivityId,
		FromEvt.ToTimestamp FromTimestamp,
		0 FromIs2Way,
		ToEvtSendPort.nEventId ToEventId,
		CASE 
			WHEN ToEvt.strServiceType = 'Orchestration' AND ToEvtSendPort.strServiceType = 'Pipeline' THEN ToEvt.uidMessageInstanceId
			WHEN ToEvt.strServiceType = 'Orchestration' AND ToEvtSendPort.strServiceType = 'Orchestration'  THEN ToEvtSendPort.uidMessageInstanceId
			ELSE ToEvt.uidMessageInstanceId
		END ToMessageInstanceId,
		CASE 
			WHEN ToEvt.strServiceType = 'Orchestration' AND ToEvtSendPort.strServiceType = 'Pipeline' THEN 
			ToEvt.uidServiceInstanceId
			WHEN ToEvt.strServiceType = 'Orchestration' AND ToEvtSendPort.strServiceType = 'Orchestration'  THEN 
			ToEvtSendPort.uidServiceInstanceId
			ELSE ToEvt.uidServiceInstanceId
		END 
		ToServiceInstanceId,
		ToEvt.nStatus ToStatus,
		CASE 
			WHEN ToEvtSendPort.strServiceType = 'Orchestration' THEN ToEvtSendPort.strServiceName 
			ELSE ToEvtSendPort.strPortName
		END  ToComponentName,
		ToEvtSendPort.strServiceName ToServiceName,
		ToEvtSendPort.strServiceType ToServiceType,
		ToEvtSendPort.uidActivityId ToActivityId,
		ToEvtSendPort.dtTimestamp ToTimestamp,
		ToEvtSendPort.uidServiceInstanceId MessageServiceInstance,
		FromEvt.FlowId,
		FromEvt.Generation + 1 Generation
	From 
			cteLinks FromEvt
		INNER JOIN cteLinkedEvents ToEvt
			ON FromEvt.ToMessageInstanceId <> ToEvt.uidMessageInstanceId
			AND FromEvt.FromServiceInstanceId <> ToEvt.uidServiceInstanceId
			AND FromEvt.ToServiceInstanceId = ToEvt.uidServiceInstanceId
			AND ToEvt.nStatus = 1
		INNER JOIN cteLinkedEvents ToEvtSendPort
			ON ToEvt.uidMessageInstanceId = ToEvtSendPort.uidMessageInstanceId
			AND ToEvt.uidServiceInstanceId <> ToEvtSendPort.uidServiceInstanceId
			AND ToEvt.nStatus = 1 AND ToEvtSendPort.nStatus = 0
	Where FromEvt.FromServiceInstanceId  <>  ToEvtSendPort.uidServiceInstanceId 
	
--2WaySendPort link to previous service
UNION ALL
	Select
		FromEvt.FowTemplate FowTemplate,
		fromEvt.ToEventId FromEventId,
		FromEvt.ToMessageInstanceId FromMessageInstanceId,
		FromEvt.ToServiceInstanceId FromServiceInstanceId,
		FromEvt.ToStatus FromStatus,
		FromEvt.ToComponentName FromComponentName,
		FromEvt.ToServiceName FromServiceName,
		FromEvt.ToServiceType FromServiceType,
		FromEvt.ToActivityId FromActivityId,
		FromEvt.ToTimestamp FromTimestamp,
		1 FromIs2Way,
		ToEvtSendPort2.nEventId ToEventId,
		ToEvtSendPort2.uidMessageInstanceId ToMessageInstanceId,
		FromEvt.FromMessageInstanceId ToServiceInstanceId,
		ToEvtSendPort2.nStatus ToStatus,
		CASE 
			WHEN ToEvtSendPort2.strServiceType = 'Orchestration' THEN ToEvtSendPort2.strServiceName 
			ELSE ToEvtSendPort2.strPortName
		END  ToComponentName,
		ToEvtSendPort2.strServiceName ToServiceName,
		ToEvtSendPort2.strServiceType ToServiceType,
		ToEvtSendPort2.uidActivityId ToActivityId,
		ToEvtSendPort2.dtTimestamp ToTimestamp,
		ToEvtSendPort2.uidServiceInstanceId  MessageServiceInstance,
		FromEvt.FlowId,
		FromEvt.Generation + 1 Generation
	From 
		cteLinks FromEvt
		INNER JOIN cteLinkedEvents ToEvtSendPort
			ON FromEvt.ToMessageInstanceId <> ToEvtSendPort.uidMessageInstanceId 
			AND FromEvt.MessageServiceInstance = ToEvtSendPort.uidServiceInstanceId
			AND FromEvt.ToActivityId <> ToEvtSendPort.uidActivityId
			AND FromEvt.ToStatus = 1
			AND ToEvtSendPort.nStatus = 0
			AND FromEvt.ToTimestamp <= ToEvtSendPort.dtTimestamp -- ensure we only link those that follow
		INNER JOIN cteLinkedEvents ToEvt
			ON 
			FromEvt.ToMessageInstanceId <> ToEvt.uidMessageInstanceId
			AND	FromEvt.FromServiceInstanceId = ToEvt.uidServiceInstanceId
			AND ToEvt.nStatus = 0
		INNER JOIN cteLinkedEvents ToEvtSendPort2
			ON ToEvt.uidMessageInstanceId = ToEvtSendPort2.uidMessageInstanceId
			AND FromEvt.FromServiceInstanceId = ToEvtSendPort2.uidServiceInstanceId
			AND FromEvt.FromMessageInstanceId = ToEvtSendPort2.uidMessageInstanceId
)

	Insert into @Links
	Select 
		FowTemplate, 
		FromEventId,
		FromMessageInstanceId,
		FromServiceInstanceId,
		FromStatus,
		CASE 
			WHEN link.FromServiceType = 'Orchestration' THEN SrvFrom.nServiceId
			ELSE PortFrom.nPortId
		END  FromComponentId,
		FromComponentName,
		FromServiceName,
		FromServiceType,
		FromActivityId ,
		FromTimestamp,
		FromIs2Way,
		ToEventId,
		ToMessageInstanceId,
		ToServiceInstanceId,
		ToStatus,
		CASE 
			WHEN link.ToServiceType = 'Orchestration' THEN SrvTo.nServiceId
			ELSE PortTo.nPortId
		END  ToComponentName,
		ToComponentName,
		ToServiceName,
		ToServiceType,
		ToActivityId,
		ToTimestamp,
		MessageServiceInstance,
		FlowId,
		Generation
	From cteLinks link
		LEFT JOIN dbo.dta_Services SrvFrom	WITH (NOLOCK) ON link.FromComponentName = SrvFrom.strServiceName	AND link.FromServiceType = 'Orchestration'
		LEFT JOIN dbo.dta_Services SrvTo	WITH (NOLOCK) ON link.ToComponentName = SrvTo.strServiceName		AND link.ToServiceType = 'Orchestration'
		LEFT JOIN dbo.dta_PortName PortFrom WITH (NOLOCK) ON link.FromComponentName = PortFrom.strPortName	AND link.FromServiceType = 'Pipeline'
		LEFT JOIN dbo.dta_PortName PortTo	WITH (NOLOCK) ON link.ToComponentName = PortTo.strPortName			AND link.ToServiceType = 'Pipeline';

	--Agregation of flux instances 
	Select l.FowTemplate
			, l.FromComponentId
			, l.FromComponentName
			, l.FromServiceName
			, l.FromServiceType
			, MAX(l.FromIs2Way) IsTwoWay
			, l.ToComponentId
			, l.ToComponentName
			, l.ToServiceName
			, l.ToServiceType
			--, 0  FlowCount --COUNT (Distinct l.FlowId) FlowCount
			,COUNT(*) PortCount 
	From @Links l
	Group by l.FowTemplate
			, l.FromComponentId
			, l.FromComponentName
			, l.FromServiceName
			, l.FromServiceType
			, l.ToComponentId
			, l.ToComponentName
			, l.ToServiceName
			, l.ToServiceType
	Order by l.FowTemplate, MAX(l.Generation);
	
	IF @includeArtefacts = 1
		--ReceivePort
		Select l.FromComponentId	AS ComponentId
					, l.FromComponentName	AS ComponentName
					, 'ReceivePort'			AS ComponentType
					, rp.nID				AS BizTalkMgmtDbComponentId
					, ap.nID				AS ApplicationId
					, ap.nvcName			AS ApplicationName
					, rp.bTwoWay			AS TwoWay
		From @Links l
			INNER JOIN BizTalkMgmtDb.dbo.bts_receiveport rp WITH (NOLOCK) ON l.FromComponentName = rp.nvcName
			INNER JOIN BizTalkMgmtDb.dbo.bts_application ap WITH (NOLOCK) ON rp.nApplicationID = ap.nID
		Where l.FromServiceType = 'Pipeline' --AND l.FromComponentName not in (Select FromComponentName from @Links where FromIs2Way=1)
		UNION 
		--SendPort
		Select l.ToComponentId 
					, l.ToComponentName 
					, 'SendPort'
					, sp.nID
					, ap.nID			
					, ap.nvcName		
					, sp.bTwoWay		 
		From @Links l
			INNER JOIN BizTalkMgmtDb.dbo.bts_sendport sp WITH (NOLOCK) ON l.ToComponentName = sp.nvcName
			INNER JOIN BizTalkMgmtDb.dbo.bts_application ap WITH (NOLOCK) ON sp.nApplicationID = ap.nID
		Where l.ToServiceType = 'Pipeline' 
		UNION
		--Orchestration (in From)
		Select l.FromComponentId	
					, l.FromComponentName	
					, 'Orchestration'
					, orch.nID		
					, ap.nID				
					, ap.nvcName			
					, 0						
		From @Links l
			INNER JOIN BizTalkMgmtDb.dbo.bts_orchestration orch WITH (NOLOCK) ON orch.nvcFullName = l.FromComponentName
			INNER JOIN BizTalkMgmtDb.dbo.bts_assembly abl WITH (NOLOCK) ON orch.nAssemblyID = abl.nID
			INNER JOIN BizTalkMgmtDb.dbo.bts_application ap WITH (NOLOCK) ON abl.nApplicationID = ap.nID
		Where l.FromServiceType = 'Orchestration'
		UNION
		--Orchestration (in To)
		Select l.ToComponentId	
					, l.ToComponentName	
					, 'Orchestration'
					, orch.nID		
					, ap.nID				
					, ap.nvcName			
					, 0						
		From @Links l
			INNER JOIN BizTalkMgmtDb.dbo.bts_orchestration orch WITH (NOLOCK) ON orch.nvcFullName = l.ToComponentName
			INNER JOIN BizTalkMgmtDb.dbo.bts_assembly abl WITH (NOLOCK) ON orch.nAssemblyID = abl.nID
			INNER JOIN BizTalkMgmtDb.dbo.bts_application ap WITH (NOLOCK) ON abl.nApplicationID = ap.nID
		Where l.ToServiceType = 'Orchestration';
			
	--Return Flow instances 
	IF @includeLinkInstances = 1
		SELECT * 
		--into Onatica_FlowInstances 
		FROM @Links l;
END